﻿using System;
using System.Collections.Generic;
using System.Linq;
using Crosland.Automotriz.Entidad;
using System.Data.SqlClient;
using System.Collections;

namespace Crosland.Automotriz.Data.Dao.Linq
{
    public class clsTD_ClienteDAO
    {
        public TD_CLIENTE Listar(int ID_CLIENTE)
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            List<TD_CLIENTE> lista = new List<TD_CLIENTE>();
            string error = "";
            try
            {
                var q = from c in ContextoDeObjs.TD_CLIENTE
                        where (c.ID_CLIENTE == ID_CLIENTE)
                        select c;
                lista = q.ToList();
                return lista[0];
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return null;
            }
        }



        public List<TD_CLIENTE> ListarRazonesSociales(int Id_Cliente)
        {

            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            List<TD_CLIENTE> listar = new List<TD_CLIENTE>();
            try
            {
                var r = from a in ContextoDeObjs.TD_CLIENTE
                        where (a.ID_CLIENTE_SUP == Id_Cliente)
                        select a;
                listar = r.ToList();

            }
            catch (Exception)
            {
                
            }
            return listar;
        }

        public IEnumerable<Object> ListarRazonesSocialesNoAgrupadas(int Id_Cliente, string DES_CLIENTE, string NIF, int filas) 
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            IEnumerable<Object> listaq = new List<Object>();
            try
            {
                var q = (from c in ContextoDeObjs.TD_CLIENTE
                         where (c.ID_CLIENTE_SUP != Id_Cliente && c.ID_CLIENTE!= Id_Cliente && c.DES_CLIENTE.Contains(DES_CLIENTE) && c.NIF.Contains(NIF))
                         select new { c.ID_CLIENTE, c.DES_CLIENTE, c.NIF }).Take(filas);
                listaq = q.OrderBy(x => x.DES_CLIENTE).ToList();
            }
            catch (Exception)
            {
                
            }
            return listaq;
        }



        public IEnumerable<Object> ListarClientePorNombre(string DES_CLIENTE, string NIF, int filas)
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            IEnumerable<Object> listaq = new List<Object>();
            try
            {
                var q = (from c in ContextoDeObjs.TD_CLIENTE
                         where (c.DES_CLIENTE.Contains(DES_CLIENTE) && c.NIF.Contains(NIF))
                         select new
                         {
                             c.ID_CLIENTE,
                             c.DES_CLIENTE,
                             c.NIF,
                             c.DES_DIRECCION,
                             c.Departamento,
                             c.Distrito,
                             c.Provincia,
                             c.COD_CLIENTE
                         }).Take(filas);
                listaq = q.OrderBy(x => x.DES_CLIENTE).ToList();
            }
            catch (Exception)
            {

            }
            return listaq;
        }



        public List<TD_CLIENTE> ListarClientePorCodigo(String Codigo)
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            List<TD_CLIENTE> listaq = new List<TD_CLIENTE>();
            try
            {
                var q = from c in ContextoDeObjs.TD_CLIENTE
                        where c.COD_CLIENTE == Codigo
                        select c;
                listaq = q.Take(1).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return listaq;
        }


        public IEnumerable<Object> ListarRazonesSocialesHijas(int Id_Cliente)
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            IEnumerable<Object> listaq = new List<Object>();
            try
            {
                var q = (from c in ContextoDeObjs.TD_CLIENTE
                         where (c.ID_CLIENTE_SUP == Id_Cliente && c.ID_CLIENTE != Id_Cliente)
                         select new { c.ID_CLIENTE, c.DES_CLIENTE, c.NIF });
                listaq = q.OrderBy(x => x.DES_CLIENTE).ToList();
            }
            catch (Exception)
            {

            }
            return listaq;
        }

        public IEnumerable<Object> listarCliente() { 
        
      Crosland.Automotriz.Core.AutomotrizEntities ContextoDeObjs = new Crosland.Automotriz.Core.AutomotrizEntities();

      IEnumerable<Object> list = new List<Object>();
      try
      {
          var q = (from c in ContextoDeObjs.TD_CLIENTE
                  select new { 
                  c.ID_CLIENTE,
                  c.COD_CLIENTE,
                  c.DES_CLIENTE
                  }).Take(100);



          list = q.OrderBy(x=>x.DES_CLIENTE).ToList();


      }
      catch(Exception ex) 
      {
          throw ex;
      
      }


      return list;

        }

        public bool ActualizarRelacion(TD_CLIENTE Obj)
        {
            try
            {
                DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
                ContextoDeObjs.ExecuteStoreCommand("UPDATE TD_CLIENTE SET ID_CLIENTE_SUP = " + Obj.ID_CLIENTE_SUP.ToString() + "  WHERE ID_CLIENTE = " + Obj.ID_CLIENTE.ToString());
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool ActualizarNombreComercial(TD_CLIENTE Obj)
        {
            try
            {
                DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
                ContextoDeObjs.ExecuteStoreCommand("UPDATE TD_CLIENTE SET NOM_COMERCIAL = '" + Obj.NOM_COMERCIAL + "'  WHERE ID_CLIENTE = " + Obj.ID_CLIENTE.ToString());
                ContextoDeObjs.ExecuteStoreCommand("UPDATE TD_CLIENTE SET NOM_COMERCIAL = '" + Obj.NOM_COMERCIAL + "'  WHERE ID_CLIENTE_SUP = " + Obj.ID_CLIENTE.ToString());
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public IEnumerable<Object> ListarClientes(string nombre)
        {
            Crosland.Automotriz.Core.AutomotrizEntities ContextoDeObjs = new Crosland.Automotriz.Core.AutomotrizEntities();



            try
            {
                IEnumerable<Object> Lista = new List<Object>();

                var q = (from c in ContextoDeObjs.TD_CLIENTE
                        where (c.DES_CLIENTE + c.COD_CLIENTE).Contains(nombre)
                        && c.COD_CLIENTE.Length>=10
                        select new
                        {
                            c.ID_CLIENTE,
                            c.COD_CLIENTE,
                            c.DES_CLIENTE,
                            c.NIF,
                            TipoDoc = c.ID_TIPO_NIF == 2 ? "DNI" :
                                    c.ID_TIPO_NIF == 7 ? "RUC" : "-"
                        }).Take(100);

                Lista = q.ToList();

                return Lista;


            }
            catch (Exception)
            {

                throw;
            }
        }

        public int ObtenerUltimoID()
        {
            try
            {
                DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
                var resultado = ContextoDeObjs.TD_CLIENTE.Select(a => a.ID_CLIENTE).Max();
                return int.Parse(resultado.ToString());
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public int ObtenerUltimoIdentity(string DES_TABLA) 
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            var p = new object[] { new SqlParameter("@DES_TABLA", DES_TABLA) };
            var resultado = ContextoDeObjs.ExecuteStoreQuery<TD_Cliente_Identity>("exec USP_Select_Identity @DES_TABLA", p).AsQueryable().ToList();
            return resultado[0].ID_CLIENTE;
        }

        public bool Insertar(TD_CLIENTE Obj)
        {
            try
            {
                DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
                ContextoDeObjs.AddObject("TD_CLIENTE", Obj);
                ContextoDeObjs.SaveChanges();
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        public bool Actualizar(TD_CLIENTE Obj) 
        {
            try
            {
                DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
                ContextoDeObjs.AttachTo("TD_CLIENTE", Obj);
                ContextoDeObjs.ObjectStateManager.ChangeObjectState(Obj, System.Data.EntityState.Modified);
                ContextoDeObjs.SaveChanges();
            }
            catch (Exception)
            {
                return false;

            }
            return true;
        }

        public bool ExisteDocumentoIdentidad(string NIF) 
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();

            var r = from a in ContextoDeObjs.TD_CLIENTE
                    where (a.NIF == NIF)
                    select a;

            if (r.ToList().Count > 0) { return true; } else { return false; }
        }

        public IEnumerable<Object> BuscarClientes(string DES_CLIENTE, int filas)
        {
            DM_CROSLAND_20130417Entities ContextoDeObjs = new DM_CROSLAND_20130417Entities();
            var p = new object[] { new SqlParameter("@DES_CLIENTE", DES_CLIENTE) };
            var resultado = ContextoDeObjs.ExecuteStoreQuery<TD_CLIENTE_BUSCAR>("exec USP_TD_CLIENTE_Listar @DES_CLIENTE", p).AsQueryable().ToList().Take(filas);
            return resultado;
        }




    }
}
